/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dbwrapper;

import java.io.IOException;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.xml.sax.SAXException;
import rmiserver.Ticket;
import rmiserver.Ticket;
import rmiserver.TicketServiceImpl;
import rmiserver.TicketServiceImpl;
import rmiserver.Trip;
import rmiserver.Trip;

/**
 * Запросы к базе данных
 * @author alexxxey
 */
public class DbQuery
{ 
    /**
     * Получение списка всех доступных маршрутов
     * @return Список с объектами с информацией о маршрутах
     */
    public static ArrayList<Trip> getAllTripsQuery() throws RemoteException
    {
        ArrayList<Trip> trips = new ArrayList<>();            
        try
        {
            final Connection con = DbConnection.getConnection();                        
            
            Statement stmt = con.createStatement();                        
            try
            {
                String query = "select * from Trips";
                ResultSet rs = stmt.executeQuery(query);
                                
                while (rs.next())
                {                                                
                    Trip trip = new Trip();
                    
                    trip.setTripId(rs.getInt(1));
                    trip.setDate(rs.getDate(2));
                    trip.setTime(rs.getTime(2));
                    trip.setPlacesNum(rs.getInt(3));                                                           
                    trips.add(trip);
                }                                   
            }
            finally
            {
                stmt.close();
            }
            
            for (Trip trip : trips)
            {
                stmt = con.createStatement();                        
                try
                {
                    String query = "select DESTINATIONS.DEST from DESTINATIONS " +
                                   "where DESTINATIONS.TRIPID = " + trip.getTripId();
                    
                    ResultSet rs = stmt.executeQuery(query);
                    
                    ArrayList<String> dest = new ArrayList<>();
                    while (rs.next())             
                    {
                        dest.add(rs.getString(1));                    
                        //System.out.println(rs.getString(1));
                    }                                                        
                    
                    trip.setDestinations(dest);
                }
                finally
                {
                    stmt.close();
                }                
            }                                    
        } 
        catch (Exception ex)
        {
            Logger.getLogger(TicketServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            throw new RemoteException();
        }
        
        return trips;
    }
    
    /**
     * Запрос маршрутов по месту назначения
     * @param findDest Место назначения
     * @return Список с объектами с информацией о маршрутах
     */
    public static ArrayList<Trip> findTripsQuery(String findDest) throws RemoteException 
    {
        ArrayList<Trip> trips = new ArrayList<>();                            
        try
        {
            final Connection con = DbConnection.getConnection();     
            Statement stmt = con.createStatement();                        
            try
            {
                String query =  "select " +
                                "   Trips.id, Trips.placesNum, Trips.tripDate " +
                                "from " +
                                "   Trips, Destinations " +
                                "where " +
                                "   Trips.id = Destinations.tripId and " +
                                "   Destinations.dest = \'" + findDest + "\'";
                ResultSet rs = stmt.executeQuery(query);
                                
                while (rs.next())
                {                                                
                    Trip trip = new Trip();
                    
                    trip.setTripId(rs.getInt(1));
                    trip.setDate(rs.getDate(3));
                    trip.setTime(rs.getTime(3));
                    trip.setPlacesNum(rs.getInt(2));                                                           
                    trips.add(trip);
                }                                   
            }
            finally
            {
                stmt.close();
            }
            
            for (Trip trip : trips)
            {
                stmt = con.createStatement();                        
                try
                {
                    String query = "select DESTINATIONS.DEST from DESTINATIONS " +
                                   "where DESTINATIONS.TRIPID = " + trip.getTripId();
                    
                    ResultSet rs = stmt.executeQuery(query);
                    
                    ArrayList<String> dest = new ArrayList<>();
                    while (rs.next())             
                    {
                        dest.add(rs.getString(1));                    
                    }                                                        
                    
                    trip.setDestinations(dest);
                }
                finally
                {
                    stmt.close();
                }                
            }                                    
        } 
        catch (Exception ex)
        {
            Logger.getLogger(TicketServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            throw new RemoteException();
        }
        
        return trips;
    }
    
    /**
     * Запрос маршрутов по дате отправления
     * @param date Дата отправления
     * @return Список с объектами с информацией о маршрутах
     */
    public static ArrayList<Trip> findTripsQuery(Date date) throws RemoteException 
    {
        SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy"); 
        ArrayList<Trip> trips = new ArrayList<>();                            
        try
        {
            final Connection con = DbConnection.getConnection();     
            Statement stmt = con.createStatement();                        
            try
            {                   
                String query =  "select " +
                                "   Trips.id, Trips.placesNum, Trips.tripDate " +
                                "from " +
                                "   Trips " +
                                "where " +
                                "cast(Trips.tripDate as date) = cast(\'" + format.format(date) + "\' as date)";
                
                ResultSet rs = stmt.executeQuery(query);
                                
                while (rs.next())
                {                                                
                    Trip trip = new Trip();
                    
                    trip.setTripId(rs.getInt(1));
                    trip.setDate(rs.getDate(3));
                    trip.setTime(rs.getTime(3));
                    trip.setPlacesNum(rs.getInt(2));                                                           
                    trips.add(trip);
                }                                   
            }
            finally
            {
                stmt.close();
            }
            
            for (Trip trip : trips)
            {
                stmt = con.createStatement();                        
                try
                {
                    String query = "select DESTINATIONS.DEST from DESTINATIONS " +
                                   "where DESTINATIONS.TRIPID = " + trip.getTripId();
                    
                    ResultSet rs = stmt.executeQuery(query);
                    
                    ArrayList<String> dest = new ArrayList<>();
                    while (rs.next())             
                    {
                        dest.add(rs.getString(1));                    
                    }                                                        
                    
                    trip.setDestinations(dest);
                }
                finally
                {
                    stmt.close();
                }                
            }                                    
        } 
        catch (Exception ex)
        {
            Logger.getLogger(TicketServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            throw new RemoteException();
        }
        
        return trips;
    }
    
    /**
     * Запрос зарезервированных мест на маршрут
     * @param tripId Номер маршрута
     * @return Список зарезервированных мест
     */
    public static ArrayList<Integer> tripPlacesQuery(int tripId) throws RemoteException
    {     
        ArrayList<Integer> places = new ArrayList<>();
        try
        {
            final Connection con = DbConnection.getConnection();     
            Statement stmt = con.createStatement();                        
            try
            {                   
                String query =  "select " +
                                "   Tickets.place " +
                                "from " +
                                "   Tickets " +
                                "where " + 
                                "   Tickets.tripId = " + tripId;
                
                ResultSet rs = stmt.executeQuery(query);
                                
                while (rs.next())
                {                                                
                    places.add(rs.getInt(1));
                }                                   
            }
            finally
            {
                stmt.close();
            }                                                       
        } 
        catch (Exception ex)
        {
            Logger.getLogger(TicketServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            throw new RemoteException();
        }
                
        return places;
    }
    
    /**
     * Добавление записи в таблицу билетов (резервирование)
     * @param tk Билет
     */
    public static void insertTicketQuery(Ticket tk) throws RemoteException
    {
        try
        {
            final Connection con = DbConnection.getConnection();     
            Statement stmt = con.createStatement();                        
            try
            {                   
                String query =  "insert into Tickets ( " +
                                "   Tickets.tripId, Tickets.place, " +
                                "   Tickets.pasport, Tickets.firstName, Tickets.lastName " +
                                ") " +
                                "values ( " +
                                    tk.getTripId() + ", " + tk.getPlace() + ", " + 
                                    tk.getManId() + ", " +
                                    "\'" + tk.getFirstName() + "\'," +
                                    "\'" + tk.getLastName() + "\')";
                stmt.executeUpdate(query);                                                       
            }
            finally
            {
                stmt.close();
            }                                                       
        } 
        catch (Exception ex)
        {
            Logger.getLogger(TicketServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            throw new RemoteException();
        }
    }
    
    /**
     * Запрос списка зарезервированных на человека билетов
     * @param manId номер паспорта
     * @return Сипосик зарезервированных билетов
     */
    public static ArrayList<Ticket> getReservedTickets(int manId) throws RemoteException
    {
        ArrayList<Ticket> tickets = new ArrayList<>();
        
        try
        {
            final Connection con = DbConnection.getConnection();     
            Statement stmt = con.createStatement();                        
            try
            {                   
                String query =  "select * from Tickets " +
                                "where Tickets.pasport = " + manId;
                
                ResultSet rs = stmt.executeQuery(query);
                                
                while (rs.next())
                {    
                    Ticket tk = new Ticket(rs.getInt(1),
                            rs.getInt(2), rs.getInt(3), rs.getString(4),
                            rs.getString(5)); 
                    
                    tickets.add(tk);
                }                                   
            }
            finally
            {
                stmt.close();
            }                                                       
        } 
        catch (Exception ex)
        {
            Logger.getLogger(TicketServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            throw new RemoteException();
        }
        
        return tickets;
    }
    
    /**
     * Запрос на удаление зарезервированного билета
     * @param tk Удаляемый билет
     */
    public static void removeTicketQuery(Ticket tk) throws RemoteException
    {
        try
        {
            final Connection con = DbConnection.getConnection();     
            Statement stmt = con.createStatement();                        
            try
            {                                   
                String query =  "delete from TICKETS " +
                                "where TICKETS.TRIPID = " + tk.getTripId() + " and " +
                                "TICKETS.PLACE = " + tk.getPlace();
                
                stmt.executeUpdate(query);                                                
            }
            finally
            {
                stmt.close();
            }                                                       
        } 
        catch (Exception ex)
        {
            Logger.getLogger(TicketServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            throw new RemoteException();
        }
    }
}
